﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{    
    public partial class JLTGB14RSData : BaseFunction
    {
        ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        /*public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }

        SETINTEXDB INTEXDB = new SETINTEXDB();
         */
        public JLTGB14RSData()
            : base()
        {
        }
         public JLTGB14RSData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetGB14(DataRow dwTABEL)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "GB14";
            string FROMDATE = dwTABEL["FROMDATE"].ToString().Trim();
            string ENDDATE = dwTABEL["ENDDATE"].ToString().Trim();
            string CO = dwTABEL["CO"].ToString().Trim();
            try
            {
                orlReadCommand = INTEXDBC.OracleDB.CreateCommand();
                orlReadCommand.CommandText = " select TRIM(F.ABKKDAUFTNR) AS CO,A.GABBUCHDAT,A.GABPARTIE,A.GABART,A.GABVAR, "
                    + " TRIM(C.GSHANDLNR) AS ART,A.GABFARBE,TRIM(D.GSCBEZ) AS COLOR, "
                    + " E.GPFBPART,SUM(A.GABKG*0.001) AS KG, "
                    + " trim(G.ABAKDAUFTNR) as QTY,A.GABSP,TRIM(G.ABABEM1) AS REMARK "
                    + " from gabew a,ABPOS B,GSDAT C,GSCOLOR D,GPDAT E,ABKOPF F,ABAUFM G "
                    + " where a.gabbs = '13' and a.gabfc = 'GB20' "
                    + " AND A.GABLO = '01' AND A.GABLB = 'SS' "
                    + " AND A.GABMANDANT = B.ABPMANDANT AND A.GABAUFTYP = 'V'  "
                    + " AND A.GABAUFKRS = B.ABPAUFKRS AND A.GABAUFLFD = B.ABPAUFLFD "
                    + " AND A.GABAUFPOS = B.ABPAUFPOS AND A.GABMANDANT = C.GSMANDANT "
                    + " AND A.GABART = C.GSART AND A.GABVAR = C.GSVAR "
                    + " AND A.GABMANDANT = D.GSCMANDANT AND A.GABART = D.GSCART "
                    + " AND A.GABVAR = D.GSCVAR AND A.GABFARBE = D.GSCFARBE "
                    + " AND A.GABMANDANT = E.GPMANDANT AND A.GABPARTIE = E.GPPARTIE "
                    + " AND A.GABPARTIEKZ = E.GPPARTIEKZ AND A.GABMANDANT = F.ABKMANDANT "
                    + " AND A.GABAUFKRS = F.ABKAUFKRS AND A.GABAUFLFD = F.ABKAUFLFD "
                    + " AND A.GABMANDANT = G.ABAMANDANT AND A.GABAUFTYP = G.ABAAUFTYP "
                    + " AND A.GABAUFKRS = G.ABAAUFKRS AND A.GABAUFLFD = G.ABAAUFLFD "
                    + " AND A.GABAUFPOS = G.ABAAUFPOS AND (E.GPFBPART = G.ABAKDPARTIE  "
                    + "   OR (E.GPFBPART = G.ABAAUFKRS||G.ABAAUFLFD||LPAD(TO_NUMBER(G.ABAAUFPOS),2,'0') "
                    + "     AND G.ABAKDPARTIE = '          ')) "
                    + " AND A.GABBUCHDAT >='"+FROMDATE+"' AND A.GABBUCHDAT <= '"+ENDDATE+"' "
                    + " AND F.ABKKDAUFTNR like '"+CO+"%' "
                    + " GROUP BY A.GABBUCHDAT,A.GABPARTIE,A.GABART,A.GABVAR,C.GSHANDLNR, "
                    + " A.GABFARBE,D.GSCBEZ,E.GPFBPART,F.ABKKDAUFTNR,G.ABAKDAUFTNR "
                    + " ,A.GABSP,G.ABABEM1 "
                    + " order by CO,GABART,GABFARBE "                   
                    ;

                Result = INTEXDBC.Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ART"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildData(DataRow dwUI)
        {
            /*DataSet SResult = new DataSet();
            SETINTEXDB SReturn = new SETINTEXDB(); */
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetGB14(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }

    }
}